/**
 * 
 */
package rentReturnLogic;

import java.awt.Desktop;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javafx.collections.FXCollections;
import javafx.collections.ObservableMap;
import rentReturnEntity.CarDisplayableEntity;
import rentReturnEntity.RentEntity;
import rentReturnEntity.ReturnEntity;
import rentReturnEntity.TruckDisplayableEntity;
import rentReturnService.ReportService;

/**
 * @author Yokesh Kumar
 * @author Sashiraj Dhanarajan
 *
 */
public class ReportLogic {

	/**
	 * This method fetches the report of rented vehicles and map it to the table
	 * @return Returns Mapped values
	 * @pre The valued should be fetched from the database 
	 * @post Map the fetched values into hash map 
	 * 
	 */
	public ObservableMap<String,Integer> fetchRentReport(){
		ReportService service = new ReportService();
		ResultSet rs = null;
		ObservableMap<String,Integer> reportMap = null;
		Map<String,Integer> rentMap = new HashMap<>();
		int carTotal = 0;
		int truckTotal = 0;
		try {
			rs = service.fetchRentReport();
			if(rs != null){
				while(rs.next()){
					rentMap.put(rs.getString("vehicleType"), rs.getInt("numberofVehicles"));
					if(rs.getString("vehicleType").equalsIgnoreCase("Economy") ||
							rs.getString("vehicleType").equalsIgnoreCase("Premium") ||
							rs.getString("vehicleType").equalsIgnoreCase("Compact") ||
							rs.getString("vehicleType").equalsIgnoreCase("Mid-Size") ||
							rs.getString("vehicleType").equalsIgnoreCase("Standard") ||
							rs.getString("vehicleType").equalsIgnoreCase("Luxury") ||
							rs.getString("vehicleType").equalsIgnoreCase("SUV") ||
							rs.getString("vehicleType").equalsIgnoreCase("VAN") ){
						carTotal = carTotal + rs.getInt("numberofVehicles");
					}
					if(rs.getString("vehicleType").equalsIgnoreCase("24-Foot") ||
							rs.getString("vehicleType").equalsIgnoreCase("15-Foot") ||
							rs.getString("vehicleType").equalsIgnoreCase("12-Foot") ||
							rs.getString("vehicleType").equalsIgnoreCase("BoxTruck") ||
							rs.getString("vehicleType").equalsIgnoreCase("CargoVan")){
						truckTotal = truckTotal + rs.getInt("numberofVehicles");
					}

				}
				rentMap.put("CarTotal", carTotal);
				rentMap.put("TruckTotal", truckTotal);
				rentMap.put("Total", carTotal+truckTotal);
				System.out.println("CarTotal" + carTotal);
				System.out.println("TruckTotal" + truckTotal);

				reportMap = FXCollections.observableMap(rentMap);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return reportMap;
	}

	/**
	 * This method fetches the report of returned vehicles nd map it to the table
	 * @return Returns Mapped values
	 * @pre The valued should be fetched from the database 
	 * @post Map the fetched values into hash map 
	 */

	public ObservableMap<String,Integer> fetchReturnReport(){
		ReportService service = new ReportService();
		ResultSet rs = null;
		ObservableMap<String,Integer> reportMap = null;
		Map<String,Integer> returnMap = new HashMap<>();
		int carTotal = 0;
		int truckTotal = 0;
		try{
			rs = service.fetchReturnReport();
			if(rs != null){
				while(rs.next()){
					returnMap.put(rs.getString("vehicleType"), rs.getInt("numberofVehicles"));
					if(rs.getString("vehicleType").equalsIgnoreCase("Economy") ||
							rs.getString("vehicleType").equalsIgnoreCase("Premium") ||
							rs.getString("vehicleType").equalsIgnoreCase("Compact") ||
							rs.getString("vehicleType").equalsIgnoreCase("Mid-Size") ||
							rs.getString("vehicleType").equalsIgnoreCase("Standard") ||
							rs.getString("vehicleType").equalsIgnoreCase("Luxury") ||
							rs.getString("vehicleType").equalsIgnoreCase("SUV") ||
							rs.getString("vehicleType").equalsIgnoreCase("VAN") ){
						carTotal = carTotal + rs.getInt("numberofVehicles");
					}
					if(rs.getString("vehicleType").equalsIgnoreCase("24-Foot") ||
							rs.getString("vehicleType").equalsIgnoreCase("15-Foot") ||
							rs.getString("vehicleType").equalsIgnoreCase("12-Foot") ||
							rs.getString("vehicleType").equalsIgnoreCase("BoxTruck") ||
							rs.getString("vehicleType").equalsIgnoreCase("CargoVan")){
						System.out.println("vehicle type "+ rs.getString("vehicleType"));
						truckTotal = truckTotal + rs.getInt("numberofVehicles");
					}
				}
				returnMap.put("CarTotal", carTotal);
				returnMap.put("TruckTotal", truckTotal);
				returnMap.put("Total", carTotal+truckTotal);
				reportMap = FXCollections.observableMap(returnMap);
			}
		}
		catch(SQLException e){
			e.printStackTrace();
		}
		return reportMap;

	}
	
	public ArrayList<CarDisplayableEntity> fetchCarOverallVehicleList() throws SQLException
	{
		
		ReportService rs = new ReportService();
		ArrayList<CarDisplayableEntity> carArray = new ArrayList<CarDisplayableEntity>();
		carArray.add(rs.fetchCarOverallVehicleList("Purchased"));
		carArray.add(rs.fetchCarOverallVehicleList("Rented"));
		carArray.add(rs.fetchCarOverallVehicleList("Sale"));
		return carArray;
	}
	
	public ArrayList<TruckDisplayableEntity> fetchTruckOverallVehicleList() throws SQLException
	{
		
		ReportService rs = new ReportService();
		ArrayList<TruckDisplayableEntity> truckArray = new ArrayList<TruckDisplayableEntity>();
		truckArray.add(rs.fetchTruckOverallVehicleList("Purchased"));
		truckArray.add(rs.fetchTruckOverallVehicleList("Rented"));
		truckArray.add(rs.fetchTruckOverallVehicleList("Sale"));
		return truckArray;
	}
	

	/**
	 * This method is used for creating excel sheet
	 * @param reportMap
	 * @param value
	 * @pre The export to excel should be invoked
	 * @post Creates new workbook 
	 */
	public void exportToExcel(ObservableMap<String, Integer> reportMap,String value) {
		
		Boolean filePresent = false;
		Boolean sheetPresent = false;
		FileInputStream fileInput = null;
		XSSFWorkbook workbook = null;
		Calendar cal = Calendar.getInstance();
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		String date = format.format(cal.getTime());
		String fileName = "Report_".concat(date).concat(".xlsx");
		try {
			fileInput = new FileInputStream(fileName);
			System.out.println("file found");
			filePresent = true;
		} catch (FileNotFoundException e1) {
			System.out.println("file not found");
			filePresent = false;
		}

		if(filePresent){
			try {
				workbook = new XSSFWorkbook(fileInput);
				fileInput.close();
				int sheetCount = workbook.getNumberOfSheets();
				for(int i=0; i<sheetCount;i++){
					if(workbook.getSheetName(i).equalsIgnoreCase(value)){
						sheetPresent = true;
					}
				}
			} catch (IOException e) {
				e.printStackTrace();
			} 
			if(sheetPresent){
				XSSFSheet sheet = workbook.getSheet(value);
				sheet.removeRow(sheet.getRow(0));
				System.out.println("Deleting row 1");
				sheet.removeRow(sheet.getRow(1));
				System.out.println("Deleting row 2");

				workbook = populateExcel(workbook, sheet,reportMap);
			}
			else{
				XSSFSheet sheet = workbook.createSheet(value);
				workbook = populateExcel(workbook, sheet,reportMap);
			}

			try
			{
				//Write the workbook in file system
				FileOutputStream out = new FileOutputStream(fileName);
				workbook.write(out);
				out.close();
				System.out.println("excel updated successfully");
				if (Desktop.isDesktopSupported()) {
		             try {
		                 File myFile = new File(fileName);
		                 Desktop.getDesktop().open(myFile);
		             } catch (Exception e) {
		                 System.out.println(e.getMessage());
		             }
		         }
			}
			catch (Exception e)
			{
				e.printStackTrace();
			}

		}
		else{
			//Blank workbook
			workbook = new XSSFWorkbook();   
			//Create a blank sheet
			XSSFSheet sheet = workbook.createSheet(value);
			workbook = populateExcel(workbook, sheet,reportMap);
			try
			{
				//Write the workbook in file system
				FileOutputStream out = new FileOutputStream(new File(fileName));
				workbook.write(out);
				out.close();
				System.out.println("New excel successfully created");
				if (Desktop.isDesktopSupported()) {
		             try {
		                 File myFile = new File(fileName);
		                 Desktop.getDesktop().open(myFile);
		             } catch (Exception e) {
		                 System.out.println(e.getMessage());
		             }
		         }
			}
			catch (Exception e)
			{
				e.printStackTrace();
			}
		}

	}
	/**
	 * This methos id used to populate the values into the workbook
	 * @param workbook
	 * @param sheet
	 * @param reportMap
	 * @return Workbook with values is returned
	 * @pre Excel sheet should be created
	 * @post Values are stored into the excel sheet.
	 */
	public XSSFWorkbook populateExcel(XSSFWorkbook workbook,XSSFSheet sheet,ObservableMap<String, Integer> reportMap){
		//Iterate over data and write to sheet
		Set<String> keyset = reportMap.keySet();
		CellStyle style = workbook.createCellStyle();
		style.setAlignment(CellStyle.ALIGN_CENTER);
		Font font = workbook.createFont();
		style.setWrapText(true);
		int col = 0;
		Row row = sheet.createRow(0);
		for(String key : keyset){
			style.setFont(font);
			if(!key.equalsIgnoreCase("Total")){
				Cell row1Cell = row.createCell(col++);
				row1Cell.setCellValue(key);
				row1Cell.setCellStyle(style);
			}
		}
		Cell row1Cell = row.createCell(col++);
		row1Cell.setCellValue("Total");
		row1Cell.setCellStyle(style);		

		Row row1 = sheet.createRow(1);
		col = 0;
		for(String key : keyset){
			style.setFont(font);
			if(!key.equalsIgnoreCase("Total")){
				Cell row2Cell = row1.createCell(col++);
				row2Cell.setCellValue(reportMap.get(key));
				row2Cell.setCellStyle(style);
			}
		}
		Cell row2Cell = row1.createCell(col++);
		row2Cell.setCellValue(reportMap.get("Total"));
		row1Cell.setCellStyle(style);

		return workbook;
	}


}


